﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Catering_DBOpertion;
using System.Data.SqlClient;

namespace Catering_Operation
{
    public partial class DataViewForReport
    {

        #region 消耗计算表
        /// <summary>
        /// 消耗计算表 年条件
        /// </summary>
        /// <returns></returns>
        public DataTable GetXiaohao_Cal(string where)
        {
            string sql = "select F.RAWID,F.RAWName,F.RTID,F.RTName,F.RInfoID,F.RInfoName,";
            sql += "sum(Case F.[Month] when '1' then F.SumNumber else 0 end) AS '1r',";
            sql += "sum(Case F.[Month] when '2' then F.SumNumber else 0 end) AS '2r',";
            sql += "sum(Case F.[Month] when '3' then F.SumNumber else 0 end) AS '3r',";
            sql += "sum(Case F.[Month] when '4' then F.SumNumber else 0 end) AS '4r',";
            sql += "sum(Case F.[Month] when '5' then F.SumNumber else 0 end) AS '5r',";
            sql += "sum(Case F.[Month] when '6' then F.SumNumber else 0 end) AS '6r',";
            sql += "sum(Case F.[Month] when '7' then F.SumNumber else 0 end) AS '7r',";
            sql += "sum(Case F.[Month] when '8' then F.SumNumber else 0 end) AS '8r',";
            sql += "sum(Case F.[Month] when '9' then F.SumNumber else 0 end) AS '9r',";
            sql += "sum(Case F.[Month] when '10' then F.SumNumber else 0 end) AS '10r',";
            sql += "sum(Case F.[Month] when '11' then F.SumNumber else 0 end) AS '11r',";
            sql += "sum(Case F.[Month] when '12' then F.SumNumber else 0 end) AS '12r'";
            sql += " from View_Xiaohao_CalBase AS F WHERE 1=1 " + where;
            sql += " group by F.RTID,F.RTName,F.RInfoID,F.RInfoName,F.RAWID,F.RAWName";
            return DbHelperSQL.Query(sql).Tables[0];
        }

        /// <summary>
        /// 消耗计算表 月条件
        /// </summary>
        /// <returns></returns>
        public DataTable GetXiaohao_CalForMonth(string where)
        {
            string sql = "select F.RAWID,F.RAWName,F.RTID,F.RTName,F.RInfoID,F.RInfoName,";
            sql += "sum(Case F.[Day] when '1' then F.SumNumber else 0 end) AS 'R1',";
            sql += "sum(Case F.[Day] when '2' then F.SumNumber else 0 end) AS 'R2',";
            sql += "sum(Case F.[Day] when '3' then F.SumNumber else 0 end) AS 'R3',";
            sql += "sum(Case F.[Day] when '4' then F.SumNumber else 0 end) AS 'R4',";
            sql += "sum(Case F.[Day] when '5' then F.SumNumber else 0 end) AS 'R5',";
            sql += "sum(Case F.[Day] when '6' then F.SumNumber else 0 end) AS 'R6',";
            sql += "sum(Case F.[Day] when '7' then F.SumNumber else 0 end) AS 'R7',";
            sql += "sum(Case F.[Day] when '8' then F.SumNumber else 0 end) AS 'R8',";
            sql += "sum(Case F.[Day] when '9' then F.SumNumber else 0 end) AS 'R9',";
            sql += "sum(Case F.[Day] when '10' then F.SumNumber else 0 end) AS 'R10',";
            sql += "sum(Case F.[Day] when '11' then F.SumNumber else 0 end) AS 'R11',";
            sql += "sum(Case F.[Day] when '12' then F.SumNumber else 0 end) AS 'R12',";
            sql += "sum(Case F.[Day] when '13' then F.SumNumber else 0 end) AS 'R13',";
            sql += "sum(Case F.[Day] when '14' then F.SumNumber else 0 end) AS 'R14',";
            sql += "sum(Case F.[Day] when '15' then F.SumNumber else 0 end) AS 'R15',";
            sql += "sum(Case F.[Day] when '16' then F.SumNumber else 0 end) AS 'R16',";
            sql += "sum(Case F.[Day] when '17' then F.SumNumber else 0 end) AS 'R17',";
            sql += "sum(Case F.[Day] when '18' then F.SumNumber else 0 end) AS 'R18',";
            sql += "sum(Case F.[Day] when '19' then F.SumNumber else 0 end) AS 'R19',";
            sql += "sum(Case F.[Day] when '20' then F.SumNumber else 0 end) AS 'R20',";
            sql += "sum(Case F.[Day] when '21' then F.SumNumber else 0 end) AS 'R21',";
            sql += "sum(Case F.[Day] when '22' then F.SumNumber else 0 end) AS 'R22',";
            sql += "sum(Case F.[Day] when '23' then F.SumNumber else 0 end) AS 'R23',";
            sql += "sum(Case F.[Day] when '24' then F.SumNumber else 0 end) AS 'R24',";
            sql += "sum(Case F.[Day] when '25' then F.SumNumber else 0 end) AS 'R25',";
            sql += "sum(Case F.[Day] when '26' then F.SumNumber else 0 end) AS 'R26',";
            sql += "sum(Case F.[Day] when '27' then F.SumNumber else 0 end) AS 'R27',";
            sql += "sum(Case F.[Day] when '28' then F.SumNumber else 0 end) AS 'R28',";
            sql += "sum(Case F.[Day] when '29' then F.SumNumber else 0 end) AS 'R29',";
            sql += "sum(Case F.[Day] when '30' then F.SumNumber else 0 end) AS 'R30',";
            sql += "sum(Case F.[Day] when '31' then F.SumNumber else 0 end) AS 'R31'";
            sql += " from View_Xiaohao_CalBase AS F WHERE 1=1 " + where;
            sql += " group by F.RTID,F.RTName,F.RInfoID,F.RInfoName,F.RAWID,F.RAWName";
            return DbHelperSQL.Query(sql).Tables[0];
        }

        /// <summary>
        /// 获取库存盘点
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public string GetInventory(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select top 1 [Balance] from [Balance] where 1=1 ");
            sql.Append(where);
            sql.Append(" order by AddDate Desc,ID DESC ");
            DataTable dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            string temp1 = "";
            if (dt != null && dt.Rows.Count > 0)
            {
                temp1 = dt.Rows[0]["Balance"].ToString();
            }
            return temp1;
        }

        public DataTable GetViewFromRecordsXiaoHaoCal(string where)
        {
            string sql = "select F.RAWID,F.RAWName,F.RTID,F.RTName,F.RInfoID,F.RInfoName,";
            sql += "sum(Case F.[Month] when '1' then F.SumNumber else 0 end) AS '1',";
            sql += "sum(Case F.[Month] when '2' then F.SumNumber else 0 end) AS '2',";
            sql += "sum(Case F.[Month] when '3' then F.SumNumber else 0 end) AS '3',";
            sql += "sum(Case F.[Month] when '4' then F.SumNumber else 0 end) AS '4',";
            sql += "sum(Case F.[Month] when '5' then F.SumNumber else 0 end) AS '5',";
            sql += "sum(Case F.[Month] when '6' then F.SumNumber else 0 end) AS '6',";
            sql += "sum(Case F.[Month] when '7' then F.SumNumber else 0 end) AS '7',";
            sql += "sum(Case F.[Month] when '8' then F.SumNumber else 0 end) AS '8',";
            sql += "sum(Case F.[Month] when '9' then F.SumNumber else 0 end) AS '9',";
            sql += "sum(Case F.[Month] when '10' then F.SumNumber else 0 end) AS '10',";
            sql += "sum(Case F.[Month] when '11' then F.SumNumber else 0 end) AS '11',";
            sql += "sum(Case F.[Month] when '12' then F.SumNumber else 0 end) AS '12'";
            sql += " from view_fromrecordsViewMonitor AS F WHERE 1=1 " + where;
            sql += " group by F.RTID,F.RTName,F.RInfoID,F.RInfoName,F.RAWID,F.RAWName";
            return DbHelperSQL.Query(sql).Tables[0];
        }

        public DataTable GetViewFromRecordsXiaoHaoCalByMonth(string where)
        {
            string sql = "select F.RAWID,F.RAWName,F.RTID,F.RTName,F.RInfoID,F.RInfoName,";
            sql += "sum(Case F.[Day] when '1' then F.SumNumber else 0 end) AS '1',";
            sql += "sum(Case F.[Day] when '2' then F.SumNumber else 0 end) AS '2',";
            sql += "sum(Case F.[Day] when '3' then F.SumNumber else 0 end) AS '3',";
            sql += "sum(Case F.[Day] when '4' then F.SumNumber else 0 end) AS '4',";
            sql += "sum(Case F.[Day] when '5' then F.SumNumber else 0 end) AS '5',";
            sql += "sum(Case F.[Day] when '6' then F.SumNumber else 0 end) AS '6',";
            sql += "sum(Case F.[Day] when '7' then F.SumNumber else 0 end) AS '7',";
            sql += "sum(Case F.[Day] when '8' then F.SumNumber else 0 end) AS '8',";
            sql += "sum(Case F.[Day] when '9' then F.SumNumber else 0 end) AS '9',";
            sql += "sum(Case F.[Day] when '10' then F.SumNumber else 0 end) AS '10',";
            sql += "sum(Case F.[Day] when '11' then F.SumNumber else 0 end) AS '11',";
            sql += "sum(Case F.[Day] when '12' then F.SumNumber else 0 end) AS '12',";
            sql += "sum(Case F.[Day] when '13' then F.SumNumber else 0 end) AS '13',";
            sql += "sum(Case F.[Day] when '14' then F.SumNumber else 0 end) AS '14',";
            sql += "sum(Case F.[Day] when '15' then F.SumNumber else 0 end) AS '15',";
            sql += "sum(Case F.[Day] when '16' then F.SumNumber else 0 end) AS '16',";
            sql += "sum(Case F.[Day] when '17' then F.SumNumber else 0 end) AS '17',";
            sql += "sum(Case F.[Day] when '18' then F.SumNumber else 0 end) AS '18',";
            sql += "sum(Case F.[Day] when '19' then F.SumNumber else 0 end) AS '19',";
            sql += "sum(Case F.[Day] when '20' then F.SumNumber else 0 end) AS '20',";
            sql += "sum(Case F.[Day] when '21' then F.SumNumber else 0 end) AS '21',";
            sql += "sum(Case F.[Day] when '22' then F.SumNumber else 0 end) AS '22',";
            sql += "sum(Case F.[Day] when '23' then F.SumNumber else 0 end) AS '23',";
            sql += "sum(Case F.[Day] when '24' then F.SumNumber else 0 end) AS '24',";
            sql += "sum(Case F.[Day] when '25' then F.SumNumber else 0 end) AS '25',";
            sql += "sum(Case F.[Day] when '26' then F.SumNumber else 0 end) AS '26',";
            sql += "sum(Case F.[Day] when '27' then F.SumNumber else 0 end) AS '27',";
            sql += "sum(Case F.[Day] when '28' then F.SumNumber else 0 end) AS '28',";
            sql += "sum(Case F.[Day] when '29' then F.SumNumber else 0 end) AS '29',";
            sql += "sum(Case F.[Day] when '30' then F.SumNumber else 0 end) AS '30',";
            sql += "sum(Case F.[Day] when '31' then F.SumNumber else 0 end) AS '31'";
            sql += " from view_fromrecordsViewMonitor AS F WHERE 1=1 " + where;
            sql += " group by F.RTID,F.RTName,F.RInfoID,F.RInfoName,F.RAWID,F.RAWName";
            return DbHelperSQL.Query(sql).Tables[0];
        }

        #endregion

        #region 消耗分类汇总
        /// <summary>
        /// 消耗分类汇总数据  根据年条件
        /// </summary>
        /// <returns></returns> 
        public DataTable GetViewSumToDataSet(string where)
        {
            StringBuilder sql = new StringBuilder();
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@strwhere",where)
            };
            return DbHelperSQL.RunProcedureReDataTable("Proc_XiaoHaoSum", parm);
        }

        public DataTable GetViewFromRecordsViewMonitor(string where)
        {
            string sql = "select F.RTID,F.RTName,F.RInfoID,F.RInfoName,";
            sql += "sum(Case F.[Month] when '1' then F.SumNumber else 0 end) AS '1',";
            sql += "sum(Case F.[Month] when '2' then F.SumNumber else 0 end) AS '2',";
            sql += "sum(Case F.[Month] when '3' then F.SumNumber else 0 end) AS '3',";
            sql += "sum(Case F.[Month] when '4' then F.SumNumber else 0 end) AS '4',";
            sql += "sum(Case F.[Month] when '5' then F.SumNumber else 0 end) AS '5',";
            sql += "sum(Case F.[Month] when '6' then F.SumNumber else 0 end) AS '6',";
            sql += "sum(Case F.[Month] when '7' then F.SumNumber else 0 end) AS '7',";
            sql += "sum(Case F.[Month] when '8' then F.SumNumber else 0 end) AS '8',";
            sql += "sum(Case F.[Month] when '9' then F.SumNumber else 0 end) AS '9',";
            sql += "sum(Case F.[Month] when '10' then F.SumNumber else 0 end) AS '10',";
            sql += "sum(Case F.[Month] when '11' then F.SumNumber else 0 end) AS '11',";
            sql += "sum(Case F.[Month] when '12' then F.SumNumber else 0 end) AS '12'";
            sql += " from view_fromrecordsViewMonitor AS F WHERE 1=1 " + where;
            sql += " group by F.RTID,F.RTName,F.RInfoID,F.RInfoName";
            return DbHelperSQL.Query(sql).Tables[0];
        }

        /// <summary>
        /// 消耗分类汇总数据  根据月条件
        /// </summary>
        /// <returns></returns> 
        public DataTable GetViewSumToDataTableForMonth(string where)
        {
            string sql = "select F.RTID,F.RTName,F.RInfoID,F.RInfoName,";
            sql += "sum(Case F.[Day] when '1' then F.SumNumber else 0 end) AS '1',";
            sql += "sum(Case F.[Day] when '2' then F.SumNumber else 0 end) AS '2',";
            sql += "sum(Case F.[Day] when '3' then F.SumNumber else 0 end) AS '3',";
            sql += "sum(Case F.[Day] when '4' then F.SumNumber else 0 end) AS '4',";
            sql += "sum(Case F.[Day] when '5' then F.SumNumber else 0 end) AS '5',";
            sql += "sum(Case F.[Day] when '6' then F.SumNumber else 0 end) AS '6',";
            sql += "sum(Case F.[Day] when '7' then F.SumNumber else 0 end) AS '7',";
            sql += "sum(Case F.[Day] when '8' then F.SumNumber else 0 end) AS '8',";
            sql += "sum(Case F.[Day] when '9' then F.SumNumber else 0 end) AS '9',";
            sql += "sum(Case F.[Day] when '10' then F.SumNumber else 0 end) AS '10',";
            sql += "sum(Case F.[Day] when '11' then F.SumNumber else 0 end) AS '11',";
            sql += "sum(Case F.[Day] when '12' then F.SumNumber else 0 end) AS '12',";
            sql += "sum(Case F.[Day] when '13' then F.SumNumber else 0 end) AS '13',";
            sql += "sum(Case F.[Day] when '14' then F.SumNumber else 0 end) AS '14',";
            sql += "sum(Case F.[Day] when '15' then F.SumNumber else 0 end) AS '15',";
            sql += "sum(Case F.[Day] when '16' then F.SumNumber else 0 end) AS '16',";
            sql += "sum(Case F.[Day] when '17' then F.SumNumber else 0 end) AS '17',";
            sql += "sum(Case F.[Day] when '18' then F.SumNumber else 0 end) AS '18',";
            sql += "sum(Case F.[Day] when '19' then F.SumNumber else 0 end) AS '19',";
            sql += "sum(Case F.[Day] when '20' then F.SumNumber else 0 end) AS '20',";
            sql += "sum(Case F.[Day] when '21' then F.SumNumber else 0 end) AS '21',";
            sql += "sum(Case F.[Day] when '22' then F.SumNumber else 0 end) AS '22',";
            sql += "sum(Case F.[Day] when '23' then F.SumNumber else 0 end) AS '23',";
            sql += "sum(Case F.[Day] when '24' then F.SumNumber else 0 end) AS '24',";
            sql += "sum(Case F.[Day] when '25' then F.SumNumber else 0 end) AS '25',";
            sql += "sum(Case F.[Day] when '26' then F.SumNumber else 0 end) AS '26',";
            sql += "sum(Case F.[Day] when '27' then F.SumNumber else 0 end) AS '27',";
            sql += "sum(Case F.[Day] when '28' then F.SumNumber else 0 end) AS '28',";
            sql += "sum(Case F.[Day] when '29' then F.SumNumber else 0 end) AS '29',";
            sql += "sum(Case F.[Day] when '30' then F.SumNumber else 0 end) AS '30',";
            sql += "sum(Case F.[Day] when '31' then F.SumNumber else 0 end) AS '31'";
            sql += " from view_fromrecordsViewMonitor AS F WHERE 1=1 " + where;
            sql += " group by F.RTID,F.RTName,F.RInfoID,F.RInfoName";
            return DbHelperSQL.Query(sql).Tables[0];
        }

        /// <summary>
        /// 营养分析
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="classId"></param>
        /// <param name="dateTime"></param>
        /// <returns></returns>
        public DataTable GetNutrientCompositionGroupByMonth(string GroupID, string classId, int year)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select SUM(CASE WHEN ([content] IS NOT NULL AND [content] <>'') THEN Convert(decimal(18,2),[content]) ELSE 0 END) AS [content],NutritionElementsID,[Elements],ClassID,GroupID,Month(date) AS [Month] FROM View_YingYangFenXiBZ WHERE 1=1 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND GroupID = {0} ", GroupID));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(string.Format(" AND ClassId = {0} ", classId));
            }
            //AND Year(outDate)='2012'
            if (year > 1949)
            {
                strSql.Append(" AND (Year(Date)) = " + year);
            }
            strSql.Append(" Group BY Groupid,classid,Month(Date),NutritionElementsID,[Elements] ");
            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        #endregion

        //<<<<<<< .mine
        //        #region 查询HistoryData
        //        public DataTable GetTotalPercentHistoryDate(string where)
        //        {
        //            StringBuilder sql = new StringBuilder();
        //            sql.Append(@"SELECT     dbo.HistoryData.RAWID as id,  SUM(dbo.HistoryData.Numbers) AS Numbers
        //FROM         dbo.HistoryData INNER JOIN
        //                      dbo.ClassInfo ON dbo.HistoryData.ClassID = dbo.ClassInfo.id INNER JOIN
        //                      dbo.RawMaterials ON dbo.HistoryData.RAWID = dbo.RawMaterials.id
        //WHERE     dbo.HistoryData.id <> - 99 ");
        //            sql.Append(where);
        //            sql.Append(@" group by dbo.HistoryData.RAWID");
        //            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        //        }
        //        public DataTable GetTotalPercentMonthHistoryDate(string where) 
        //        {
        //            StringBuilder sql = new StringBuilder();
        //            sql.Append(@"SELECT     dbo.HistoryData.RAWID as id, SUM(dbo.HistoryData.Numbers) AS Numbers, Month(dbo.HistoryData.InDate) as month
        //FROM         dbo.HistoryData INNER JOIN
        //                      dbo.ClassInfo ON dbo.HistoryData.ClassID = dbo.ClassInfo.id INNER JOIN
        //                      dbo.RawMaterials ON dbo.HistoryData.RAWID = dbo.RawMaterials.id
        //WHERE     dbo.HistoryData.id <> - 99  ");
        //            sql.Append(where);
        //            sql.Append(@" group by dbo.HistoryData.RAWID, Month(dbo.HistoryData.InDate)");
        //            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        //        }
        //        #endregion

        //=======
        #region 查询HistoryData
        public DataTable GetTotalPercentHistoryDate(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT     dbo.HistoryData.RAWID as id,  SUM(dbo.HistoryData.Numbers) AS Numbers
FROM         dbo.HistoryData INNER JOIN
                      dbo.ClassInfo ON dbo.HistoryData.ClassID = dbo.ClassInfo.id INNER JOIN
                      dbo.RawMaterials ON dbo.HistoryData.RAWID = dbo.RawMaterials.id
WHERE     dbo.HistoryData.id <> - 99 ");
            sql.Append(where);
            sql.Append(@" group by dbo.HistoryData.RAWID");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        public DataTable GetTotalPercentMonthHistoryDate(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT     dbo.HistoryData.RAWID as id, SUM(dbo.HistoryData.Numbers) AS Numbers, Month(dbo.HistoryData.InDate) as month
FROM         dbo.HistoryData INNER JOIN
                      dbo.ClassInfo ON dbo.HistoryData.ClassID = dbo.ClassInfo.id INNER JOIN
                      dbo.RawMaterials ON dbo.HistoryData.RAWID = dbo.RawMaterials.id
WHERE     dbo.HistoryData.id <> - 99  ");
            sql.Append(where);
            sql.Append(@" group by dbo.HistoryData.RAWID, Month(dbo.HistoryData.InDate)");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        #endregion

        #region 累计百分比
        /// <summary>
        /// 只返回五大类原料    
        /// 肉类、海鲜、蔬菜、粮食、水果
        /// </summary>
        /// <param name="where">查询条件</param>
        /// <returns></returns>
        public DataTable GetTotalPercent(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT     dbo.RawMaterials.id,dbo.RawMaterials.TypeName, SUM(CONVERT(decimal, dbo.FromRecords.SumNumber / 1000)) AS Numbers, YEAR(dbo.FromRecords.OutDate) AS year FROM         dbo.FromRecords INNER JOIN
                      dbo.RawMaterials ON dbo.FromRecords.RAWTID = dbo.RawMaterials.id where (dbo.RawMaterials.id = 1 or dbo.RawMaterials.id = 2 or dbo.RawMaterials.id = 3 or dbo.RawMaterials.id = 5 or dbo.RawMaterials.id = 6) ");
            sql.Append(where);
            sql.Append(" GROUP BY dbo.RawMaterials.id,dbo.RawMaterials.TypeName, YEAR(dbo.FromRecords.OutDate)");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        public DataTable GetTotalPercentMonth(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT     dbo.RawMaterials.id,dbo.RawMaterials.TypeName, SUM(CONVERT(decimal, (dbo.FromRecords.SumNumber / 1000))) AS Numbers, YEAR(dbo.FromRecords.OutDate) AS year,month(dbo.FromRecords.OutDate) AS month FROM         dbo.FromRecords INNER JOIN
                      dbo.RawMaterials ON dbo.FromRecords.RAWTID = dbo.RawMaterials.id where (dbo.RawMaterials.id = 1 or dbo.RawMaterials.id = 2 or dbo.RawMaterials.id = 3 or dbo.RawMaterials.id = 5 or dbo.RawMaterials.id = 6) ");
            sql.Append(where);
            sql.Append(" GROUP BY dbo.RawMaterials.id,dbo.RawMaterials.TypeName, YEAR(dbo.FromRecords.OutDate),month(dbo.FromRecords.OutDate)");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        public DataTable GetTotalPercentForDay(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT     dbo.RawMaterials.id,dbo.RawMaterials.TypeName, SUM(CONVERT(decimal, dbo.FromRecords.Numbers)) AS Numbers, YEAR(dbo.FromRecords.OutDate) AS year,day(dbo.FromRecords.OutDate) AS day FROM  dbo.FromRecords INNER JOIN dbo.RawMaterials ON dbo.FromRecords.RAWTID = dbo.RawMaterials.id where (dbo.RawMaterials.id = 1 or dbo.RawMaterials.id = 2 or dbo.RawMaterials.id = 3 or dbo.RawMaterials.id = 5 or dbo.RawMaterials.id = 6)  ");
            sql.Append(where);
            sql.Append(" GROUP BY dbo.RawMaterials.id,dbo.RawMaterials.TypeName, YEAR(dbo.FromRecords.OutDate),day(dbo.FromRecords.OutDate)");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        #endregion

        #region 分类统计
        public DataTable GetTypeCharDB(string strarealist, string GroupID, string[] dTime, int[] rawId, string classId)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM TypeChar where 1=1 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" and GroupID = {0}", GroupID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                strSql.Append(string.Format(" and GroupID IN ( {0} )", strarealist));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(string.Format(" and ClassId = {0}", classId));
            }
            if (dTime.Count() == 1)
            {
                strSql.Append(" and (year(outdate)) = " + dTime[0]);
            }
            else
            {
                strSql.Append(" and (");
                for (int i = 0; i < dTime.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append(" year(outdate) = " + dTime[i]);
                    }
                    else if (!string.IsNullOrEmpty(dTime[i]))
                    {
                        strSql.Append(" or year(outdate) = " + dTime[i]);
                    }
                }
                strSql.Append(" )");
            }

            if (rawId != null)
            {
                if (rawId.Count() == 1)
                {
                    strSql.Append(" and RAWTID = " + rawId[0]);
                }
                else
                {
                    strSql.Append(" and (");
                    for (int i = 0; i < rawId.Count(); i++)
                    {
                        if (i == 0)
                        {
                            strSql.Append(" RAWTID = " + rawId[i]);
                        }
                        else
                        {
                            strSql.Append(" or RAWTID = " + rawId[i]);
                        }
                    }
                    strSql.Append(" )");
                }
            }

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        public DataTable GetTypeCharDBHistoryData(string strarealist, string GroupID, string[] dTime, int[] rawId, string classId)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT     Year(HistoryData.inDate) as NumYear,HistoryData.Numbers as SumNumber,month(HistoryData.indate) as month,HistoryData.InDate as OutDate ,dbo.RawMaterials.TypeName
FROM         dbo.RawMaterials INNER JOIN
                      dbo.HistoryData ON dbo.RawMaterials.id = dbo.HistoryData.RAWID where 1=1 ");
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(string.Format(" and ClassId = {0}", classId));
            }
            if (dTime.Count() == 1)
            {
                strSql.Append(" and (year(indate)) = " + dTime[0]);
            }
            else
            {
                strSql.Append(" and (");
                for (int i = 0; i < dTime.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append(" year(indate) = " + dTime[i]);
                    }
                    else if (!string.IsNullOrEmpty(dTime[i]))
                    {
                        strSql.Append(" or year(indate) = " + dTime[i]);
                    }
                }
                strSql.Append(" )");
            }

            //if (rawId != null)
            //{
            //    if (rawId.Count() == 1)
            //    {
            //        strSql.Append(" and RAWID = " + rawId[0]);
            //    }
            //    else
            //    {
            //        strSql.Append(" and (");
            //        for (int i = 0; i < rawId.Count(); i++)
            //        {
            //            if (i == 0)
            //            {
            //                strSql.Append(" RAWID = " + rawId[i]);
            //            }
            //            else
            //            {
            //                strSql.Append(" or RAWID = " + rawId[i]);
            //            }
            //        }
            //        strSql.Append(" )");
            //    }
            //}

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }
        public DataTable GetTypeCharDBByMonth(string strarealist, string GroupID, DateTime dateTime, int[] rawId, string classId)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT *,day(outdate) AS DAY FROM TypeChar where 1=1 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" and GroupID = {0}", GroupID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                strSql.Append(string.Format(" and GroupID IN ( {0} )", strarealist));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(string.Format(" and ClassId = {0}", classId));
            }
            if (dateTime > DateTime.MinValue)
            {
                strSql.Append(" and (year(outdate)) = " + dateTime.Year);
                strSql.Append(" and (Month(outdate)) = " + dateTime.Month);
            }

            if (rawId != null)
            {
                if (rawId.Count() == 1)
                {
                    strSql.Append(" and RAWTID = " + rawId[0]);
                }
                else
                {
                    strSql.Append(" and (");
                    for (int i = 0; i < rawId.Count(); i++)
                    {
                        if (i == 0)
                        {
                            strSql.Append(" RAWTID = " + rawId[i]);
                        }
                        else
                        {
                            strSql.Append(" or RAWTID = " + rawId[i]);
                        }
                    }
                    strSql.Append(" )");
                }
            }

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }
        #endregion

        #region 分类对比
        public DataTable GetXiaohao_Contrast(string strarealist, string GroupID, string classID, string[] dTime, int[] rawId)
        {
            return new DataViewForReport().GetPerson_Avg_ContrastByYear(strarealist, dTime, GroupID, classID, rawId);//分类消耗
        }

        public DataTable GetXiaohao_ContrastForMonth(string strarealist, string GroupID, string ClassID, DateTime DTIME, int[] rawId)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT groupid,classid,cname,typename,RAWTID,sum(SumNumber) as SumNumber FROM View_Xiaohao_Contrast where 1=1 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" and GroupID = {0}", GroupID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                strSql.Append(string.Format(" and GroupID IN ( {0} )", strarealist));
            }

            if (!string.IsNullOrEmpty(ClassID) && !ClassID.Equals("-10"))
            {
                strSql.Append(string.Format(" and ClassID = {0}", ClassID));
            }

            if (DTIME != null && !DTIME.Year.Equals(1949))
            {
                strSql.Append(string.Format(" and year(outdate) = {0} and month(outdate) = {1} ", DTIME.Year, DTIME.Month));
            }
            if (rawId != null)
            {
                if (rawId.Count() == 1)
                {
                    strSql.Append(" and RAWTID = " + rawId[0]);
                }
                else
                {
                    strSql.Append(" and (");
                    for (int i = 0; i < rawId.Count(); i++)
                    {
                        if (i == 0)
                        {
                            strSql.Append(" RAWTID = " + rawId[i]);
                        }
                        else
                        {
                            strSql.Append(" or RAWTID = " + rawId[i]);
                        }
                    }
                    strSql.Append(" )");
                }
            }
            strSql.Append(" group by groupid,classid,cname,RAWTID,typename ");
            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        public DataTable GetXiaohao_ContrastForDay(string strarealist, string GroupID, string ClassID, DateTime DTIME, int[] rawId)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT groupid,classid,cname,typename,RAWTID,sum(SumNumber) as SumNumber FROM View_Xiaohao_Contrast where 1=1 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" and GroupID = {0}", GroupID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                strSql.Append(string.Format(" and GroupID IN ( {0} )", strarealist));
            }
            if (DTIME != null && !DTIME.Year.Equals(1949))
            {
                strSql.Append(string.Format(" and year(outdate) = {0} and month(outdate) = {1} and day(outdate) = {2}", DTIME.Year, DTIME.Month, DTIME.Day));
            }
            if (rawId != null)
            {
                if (rawId.Count() == 1)
                {
                    strSql.Append(" and RAWTID = " + rawId[0]);
                }
                else
                {
                    strSql.Append(" and (");
                    for (int i = 0; i < rawId.Count(); i++)
                    {
                        if (i == 0)
                        {
                            strSql.Append(" RAWTID = " + rawId[i]);
                        }
                        else
                        {
                            strSql.Append(" or RAWTID = " + rawId[i]);
                        }
                    }
                    strSql.Append(" )");
                }
            }
            strSql.Append(" group by groupid,classid,cname,RAWTID,typename ");
            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }
        #endregion

        #region 食用油统计
        public DataTable GetView_PurchaseInfo(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@" SELECT [DID],[PName],sum(Convert(int,[ProgramNumber])) as ProgramNumber,sum(Convert(int,[Requirement])) as Requirement,month([Date]) as Date  FROM [CMS_Catering].[dbo].[View_PurchaseInfo] where 1=1 ");
            sql.Append(where);
            sql.Append(" group by [CID],[DID],[PName],month([Date])");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        public DataTable GetView_PurchaseInfoForMonth(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@" SELECT     dbo.View_PurchaseInfo.DID, dbo.View_PurchaseInfo.PName, SUM(CONVERT(int, dbo.View_PurchaseInfo.ProgramNumber)) AS ProgramNumber, 
                      SUM(CONVERT(int, dbo.View_PurchaseInfo.Requirement)) AS Requirement, MONTH(dbo.View_PurchaseInfo.Date) AS Date, 
                      dbo.View_PurchaseInfo.ClassID, dbo.ClassInfo.CName
FROM         dbo.View_PurchaseInfo INNER JOIN
                      dbo.ClassInfo ON dbo.View_PurchaseInfo.ClassID = dbo.ClassInfo.id where 1=1 ");
            sql.Append(where);
            sql.Append(@" GROUP BY dbo.View_PurchaseInfo.CID, dbo.View_PurchaseInfo.DID, dbo.View_PurchaseInfo.PName, MONTH(dbo.View_PurchaseInfo.Date), 
                      dbo.View_PurchaseInfo.ClassID, dbo.ClassInfo.CName");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        public DataTable GetAteNumber(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT [yearofdate]  ,[monthofdate] ,sum([Number])as Number  FROM [CMS_Catering].[dbo].[View_Dynamic] where 1=1 ");
            sql.Append(where);
            sql.Append(" group by [yearofdate] ,[monthofdate] ");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        public DataTable GetAteNumberForMonth(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT ClassID,[yearofdate]  ,[monthofdate] ,sum([Number])as Number  FROM [CMS_Catering].[dbo].[View_Dynamic] where 1=1 ");
            sql.Append(where);
            sql.Append(" group by [yearofdate] ,[monthofdate],ClassID ");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        public DataTable GetRecords(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@" SELECT sum(Convert(int,[Numbers])) as Numbers,Month([OutDate]) as OutDate
  FROM [CMS_Catering].[dbo].[FromRecords] where 1=1");
            sql.Append(where.ToString());
            sql.Append(" group by Month([OutDate])");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        public DataTable GetRecordsForMonth(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@" SELECT ClassID,sum(Convert(int,[Numbers])) as Numbers,Month([OutDate]) as OutDate
  FROM [CMS_Catering].[dbo].[FromRecords] where 1=1");
            sql.Append(where.ToString());
            sql.Append(" group by Month([OutDate]),ClassID");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        public DataTable GetView_PurchaseInfoForYear(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT [DID],[PName],sum(Convert(int,[ProgramNumber])) as ProgramNumber,sum(Convert(int,[Requirement])) as Requirement,year([Date]) as Date  FROM [CMS_Catering].[dbo].[View_PurchaseInfo] where 1=1 ");
            sql.Append(where);
            sql.Append(" group by [CID],[DID],[PName],year([Date])");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        public DataTable GetAteNumberHallYear(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT [yearofdate]  ,sum([Number])as Eat  FROM [CMS_Catering].[dbo].[View_Dynamic] where 1=1 ");
            sql.Append(where);
            sql.Append(" group by [yearofdate] ");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        public DataTable GetRecordsHallYear(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@" SELECT sum(Convert(int,[Numbers])) as Numbers,Year([OutDate]) as OutDate
  FROM [CMS_Catering].[dbo].[FromRecords] where 1=1");
            sql.Append(where.ToString());
            sql.Append(" group by Year([OutDate])");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        #endregion

        #region 年食用油统计
        public DataTable GetOilOfYearByDate(string year)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add(new DataColumn("oilName"));
            dt.Columns.Add(new DataColumn("oilNumbers"));
            StringBuilder sql = new StringBuilder();
            sql.Append("select sum(eat)as 人数 from dynamic where date > @year and date < @year");

            StringBuilder sql1 = new StringBuilder();
            sql1.Append("SELECT sum(Convert(int,Numbers))as 消耗量 from fromrecords where outdate > @year and outdate < @year and isnumeric(Numbers) = 1");

            StringBuilder sql2 = new StringBuilder();
            sql2.Append(@"SELECT sum(Convert(int,dbo.PurchaseInfo.Requirement)) as 要料量
FROM         dbo.Purchase INNER JOIN
                      dbo.PurchaseInfo ON dbo.Purchase.id = dbo.PurchaseInfo.CID where date > @year and date < @year and isnumeric(dbo.PurchaseInfo.Requirement) = 1
");

            SqlParameter[] parm = new SqlParameter[] { new SqlParameter("@year", year) };

            dt.Rows[0]["oilName"] = "就餐人数";
            dt.Rows[0]["oilNumbers"] = DbHelperSQL.Query(sql.ToString(), parm).Tables[0].Rows[0][0].ToString();
            dt.Rows[1]["oilName"] = "消耗量";
            dt.Rows[1]["oilNumbers"] = DbHelperSQL.Query(sql1.ToString(), parm).Tables[0].Rows[0][0].ToString();
            dt.Rows[2]["oilName"] = "要料量";
            dt.Rows[2]["oilNumbers"] = DbHelperSQL.Query(sql2.ToString(), parm).Tables[0].Rows[0][0].ToString();
            return dt;
        }
        #endregion

        #region 人均消耗分类汇总


        public DataTable GetPersonNumbers(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select sum(QC+Persons+Catering) as Number from View_Dynamic where 1=1 ");
            sql.Append(where);
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        /// <summary>
        /// 人均消耗分类汇总
        /// </summary>
        /// <param name="whereToXiaohao"></param>
        /// <param name="whereToDy"></param>
        /// <returns></returns>
        public DataTable GetPersonAvgXiaohaoType(string whereToXiaohao)
        {
            //StringBuilder sql = new StringBuilder();
            //sql.Append("select TypeName,RAWZSName,NumMonth,NumYear,Sum(PerNumber) as PerNumber,Sum(SumNumber) as SumNumber,Sum(Number) as Number from View_personavgxiaohao where 1=1");
            //sql.Append(whereToXiaohao);
            //sql.Append(" group by TypeName,RAWZSName,NumMonth,NumYear");
            //return DbHelperSQL.Query(sql.ToString()).Tables[0];
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@strwhere",whereToXiaohao)
            };
            return DbHelperSQL.RunProcedureReDataTable("Proc_XiaoHaoPersonAvg", parm);
        }
        #endregion

        #region 日常监控

        /// <summary>
        /// 实际消耗值
        /// </summary>
        /// <param name="GroupID">作业区ID</param>
        /// <param name="dateTime">月份</param>
        /// <param name="rawId">原料类别</param>
        /// <param name="classId">班组ID</param>
        /// <returns></returns>
        public DataTable GetFactXiaoHao_RichangJiankong(string strclasslist, string GroupID, DateTime dateTime, int[] rawId, string classId)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select Sum(Number) AS Number,Day(outDate) AS OutDay, CName,SUM(persons+QC) AS EatNumber FROM View_RiChangJianKong WHERE 1=1 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND GroupID = {0} ", GroupID));
            }
            else if (!string.IsNullOrEmpty(strclasslist))
            {
                strSql.Append(string.Format(" AND GroupID in( {0} )", strclasslist));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(string.Format(" AND ClassId = {0} ", classId));
            }

            //AND Year(outDate)='2012' AND Month(outDate)='6'
            if (dateTime > DateTime.MinValue)
            {
                strSql.Append(" AND (Year(outDate)) = " + dateTime.Year);
                strSql.Append(" AND (Month(outDate)) = " + dateTime.Month);
            }

            if (rawId != null && rawId.Length > 0)
            {
                strSql.Append(" AND (");
                for (int i = 0; i < rawId.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append(" RAWZS = " + rawId[i]);
                    }
                    else
                    {
                        strSql.Append(" OR RAWZS = " + rawId[i]);
                    }
                }
                strSql.Append(" )");
            }

            strSql.Append(" Group BY Day(outDate),CName ");

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 标准消耗值
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="dateTime"></param>
        /// <param name="rawId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public DataTable GetBiaoZhunXiaoHao_RichangJiankong(string GroupID, DateTime dateTime, int[] rawId, string classId)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select Sum(Number) AS Number,Day(Date) AS OutDay, CName FROM View_RiChangJianKongBZ WHERE 1=1 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND GroupID = {0} ", GroupID));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(string.Format(" AND ClassId = {0} ", classId));
            }

            //AND Year(outDate)='2012' AND Month(outDate)='6'
            if (dateTime > DateTime.MinValue)
            {
                strSql.Append(" AND (Year(Date)) = " + dateTime.Year);
                strSql.Append(" AND (Month(Date)) = " + dateTime.Month);
            }

            if (rawId != null && rawId.Length > 0)
            {
                strSql.Append(" AND (");
                for (int i = 0; i < rawId.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append(" RAWZS = " + rawId[i]);
                    }
                    else
                    {
                        strSql.Append(" OR RAWZS = " + rawId[i]);
                    }
                }
                strSql.Append(" )");
            }
            strSql.Append(" Group BY Day(Date),CName ");
            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 日实际消耗值
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="dateTime"></param>
        /// <param name="rawId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public DataTable GetDayFactXiaoHao_RichangJiankong(string GroupID, DateTime dateTime, string[] rawId, string classId)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select SUM(Number) AS Number,MAX(persons+QC) AS EatNumber,RAWZS FROM View_RiChangJianKong WHERE 1=1 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND GroupID = {0} ", GroupID));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(string.Format(" AND ClassId = {0} ", classId));
            }

            //AND Year(outDate)='2012' AND Month(outDate)='6'
            if (dateTime > DateTime.MinValue)
            {
                strSql.Append(" AND (Year(outDate)) = " + dateTime.Year);
                strSql.Append(" AND (Month(outDate)) = " + dateTime.Month);
                strSql.Append(" AND (Day(outDate)) = " + dateTime.Day);
            }

            if (rawId != null && rawId.Length > 0)
            {
                strSql.Append(" AND (");
                for (int i = 0; i < rawId.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append(" RAWZS = " + rawId[i]);
                    }
                    else
                    {
                        strSql.Append(" OR RAWZS = " + rawId[i]);
                    }
                }
                strSql.Append(" )");
                strSql.Append(" Group BY RAWZS ");
            }

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 月实际消耗值
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="dateTime"></param>
        /// <param name="rawId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public DataTable GetMonthFactXiaoHao_RichangJiankong(string GroupID, DateTime dateTime, string[] rawId, string classId)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select SUM(Number) AS Number,RAWZS FROM View_RiChangJianKong WHERE 1=1 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND GroupID = {0} ", GroupID));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(string.Format(" AND ClassId = {0} ", classId));
            }

            //AND Year(outDate)='2012' AND Month(outDate)='6'
            if (dateTime > DateTime.MinValue)
            {
                strSql.Append(" AND (Year(outDate)) = " + dateTime.Year);
                strSql.Append(" AND (Month(outDate)) = " + dateTime.Month);
            }

            if (rawId != null && rawId.Length > 0)
            {
                strSql.Append(" AND (");
                for (int i = 0; i < rawId.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append(" RAWZS = " + rawId[i]);
                    }
                    else
                    {
                        strSql.Append(" OR RAWZS = " + rawId[i]);
                    }
                }
                strSql.Append(" )");
                strSql.Append(" Group BY RAWZS ");
            }

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 日标准消耗值
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="dateTime"></param>
        /// <param name="rawId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public DataTable GetDayBiaoZhunXiaoHao_RichangJiankong(string GroupID, DateTime dateTime, string[] rawId, string classId)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select SUM(Number) AS Number ,RAWZS FROM View_RiChangJianKongBZ WHERE 1=1 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND GroupID = {0} ", GroupID));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(string.Format(" AND ClassId = {0} ", classId));
            }

            //AND Year(outDate)='2012' AND Month(outDate)='6'
            if (dateTime > DateTime.MinValue)
            {
                strSql.Append(" AND (Year(Date)) = " + dateTime.Year);
                strSql.Append(" AND (Month(Date)) = " + dateTime.Month);
                strSql.Append(" AND (Day(Date)) = " + dateTime.Day);
            }

            if (rawId != null && rawId.Length > 0)
            {
                strSql.Append(" AND (");
                for (int i = 0; i < rawId.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append(" RAWZS = " + rawId[i]);
                    }
                    else
                    {
                        strSql.Append(" OR RAWZS = " + rawId[i]);
                    }
                }
                strSql.Append(" )");
                strSql.Append(" Group BY RAWZS ");
            }

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 按班组统计日实际消耗值
        /// </summary>
        /// <param name="GroupID">作业区ID</param>
        /// <param name="dateTime">月份</param>
        /// <param name="rawId">原料类别</param>
        /// <param name="classId">班组ID</param>
        /// <returns></returns>
        public DataTable GetBanZuFactXiaoHao_RichangJiankong(string strclasslist, string GroupID, DateTime dateTime, int[] rawId, string classId)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select Sum(Number) AS Number,ClassID,GroupID,RAWZS,Max(persons+QC) AS EatNumber,CName FROM View_RiChangJianKong WHERE 1=1 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND GroupID = {0} ", GroupID));
            }
            else if (!string.IsNullOrEmpty(strclasslist))
            {
                strSql.Append(string.Format(" AND GroupID in( {0} )", strclasslist));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(string.Format(" AND ClassId = {0} ", classId));
            }

            //AND Year(outDate)='2012' AND Month(outDate)='6'
            if (dateTime > DateTime.MinValue)
            {
                strSql.Append(" AND outDate = '" + dateTime + "' ");
            }

            if (rawId != null && rawId.Length > 0)
            {
                strSql.Append(" AND (");
                for (int i = 0; i < rawId.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append(" RAWZS = " + rawId[i]);
                    }
                    else
                    {
                        strSql.Append(" OR RAWZS = " + rawId[i]);
                    }
                }
                strSql.Append(" )");
            }

            strSql.Append(" Group BY GroupID,ClassID,CName,RAWZS ");

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 按班组统计日标准消耗值
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="dateTime"></param>
        /// <param name="rawId"></param>
        /// <param name="classId"></param>
        /// <returns></returns>
        public DataTable GetBanZuBiaoZhunXiaoHao_RichangJiankong(string GroupID, DateTime dateTime, int[] rawId, string classId)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select Sum(Number) AS Number,ClassID,GroupID,RAWZS,CName FROM View_RiChangJianKongBZ WHERE 1=1 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND GroupID = {0} ", GroupID));
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(string.Format(" AND ClassId = {0} ", classId));
            }

            //AND Year(outDate)='2012' AND Month(outDate)='6'
            if (dateTime > DateTime.MinValue)
            {
                strSql.Append(" AND Date = '" + dateTime + "' ");
            }

            if (rawId != null && rawId.Length > 0)
            {
                strSql.Append(" AND (");
                for (int i = 0; i < rawId.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append(" RAWZS = " + rawId[i]);
                    }
                    else
                    {
                        strSql.Append(" OR RAWZS = " + rawId[i]);
                    }
                }
                strSql.Append(" )");
            }
            strSql.Append(" Group BY GroupID,ClassID,CName,RAWZS ");
            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        #endregion

        #region 人均累计百分比
        public DataTable GetMonthOfCountAvgHistoryData(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT     dbo.ClassInfo.CName, dbo.RawMaterials.TypeName, (SUM(dbo.HistoryData.Numbers)/ dbo.HistoryData.EatNumber) AS Numbers, 
                      dbo.HistoryData.RAWID, dbo.HistoryData.ClassID, dbo.OperationArea.AreaName
FROM         dbo.ClassInfo INNER JOIN
                      dbo.HistoryData ON dbo.ClassInfo.id = dbo.HistoryData.ClassID INNER JOIN
                      dbo.RawMaterials ON dbo.HistoryData.RAWID = dbo.RawMaterials.id INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id where 1=1 ");
            sql.Append(where.ToString());
            sql.Append(@" GROUP BY dbo.HistoryData.RAWID, dbo.ClassInfo.CName, dbo.HistoryData.ClassID, dbo.RawMaterials.TypeName, dbo.OperationArea.AreaName,dbo.HistoryData.EatNumber");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        public DataTable GetMonthOfCountAvg(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@" SELECT RawID,[AreaName],ClassID,[CName],[TypeName],sum(Convert(decimal,[Numbers]))as Numbers
FROM [CMS_Catering].[dbo].[View_MonthOfCountAvg]  where 1=1  ");
            sql.Append(where);
            sql.Append(" group by RawID,[AreaName],[CName],[TypeName],ClassID");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        #endregion

        #region 人均食用油统计
        public DataTable GetPersonOilAvgHistory(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT SUM(dbo.HistoryData.Numbers) AS Numbers, dbo.HistoryData.ClassID, dbo.ClassInfo.CName, dbo.OperationArea.AreaName, 
                      SUM(dbo.HistoryData.EatNumber) AS Eat, SUM(CONVERT(decimal, dbo.HistoryData.EatNumber)) * 0.03 AS 'UsedOil', SUM(CONVERT(decimal, 
                      dbo.HistoryData.Numbers)) - SUM(CONVERT(decimal, dbo.HistoryData.EatNumber) * 0.03) AS 'ExNumber', SUM(CONVERT(decimal, 
                      dbo.HistoryData.Numbers)) / SUM(CONVERT(decimal, dbo.HistoryData.EatNumber)) AS 'PersonAvg'
FROM         dbo.HistoryData INNER JOIN
                      dbo.ClassInfo ON dbo.HistoryData.ClassID = dbo.ClassInfo.id INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id where dbo.HistoryData.RAWID = - 99 ");
            sql.Append(where);
            sql.Append(@" GROUP BY dbo.HistoryData.ClassID, dbo.ClassInfo.CName, dbo.OperationArea.AreaName");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        public DataTable GetPersonOilAvg(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT dbo.OperationArea.AreaName, dbo.ClassInfo.CName, SUM(CONVERT(decimal(18,2), dbo.FromRecords.SumNumber)) AS Numbers,");
            sql.Append(" SUM(CONVERT(decimal(18,2), dbo.Dynamic.Number)) AS Eat, SUM(CONVERT(decimal(18,2), dbo.Dynamic.Number))* 0.03  AS 'UsedOil', ");
            sql.Append(" SUM(CONVERT(decimal(18,2), dbo.FromRecords.SumNumber)/1000) - SUM(CONVERT(decimal(18,2), dbo.Dynamic.Number)* 0.03)  AS 'ExNumber',");
            sql.Append(" SUM(CONVERT(decimal(18,2), dbo.FromRecords.SumNumber)) / SUM(CONVERT(decimal(18,2), dbo.Dynamic.Number)) AS 'PersonAvg', ");
            sql.Append(" dbo.ClassInfo.id AS ClassID FROM dbo.ClassInfo INNER JOIN ");
            sql.Append(" dbo.Dynamic ON dbo.ClassInfo.id = dbo.Dynamic.ClassID INNER JOIN  ");
            sql.Append(" dbo.FromRecords ON dbo.ClassInfo.id = dbo.FromRecords.ClassID AND YEAR(dbo.Dynamic.Date) = YEAR(dbo.FromRecords.OutDate) ");
            sql.Append(" AND MONTH(dbo.FromRecords.OutDate) = MONTH(dbo.Dynamic.Date) INNER JOIN ");
            sql.Append(" dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id WHERE (dbo.FromRecords.RAWIDs = 20 OR dbo.FromRecords.RAWIDs = 25) ");
            sql.Append(where);
            sql.Append(" GROUP BY dbo.OperationArea.AreaName, dbo.ClassInfo.CName, dbo.ClassInfo.id, YEAR(dbo.FromRecords.OutDate), MONTH(dbo.FromRecords.OutDate)");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        public DataTable GerPersonOilAvgHistoryGroupByClassID(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT SUM(dbo.HistoryData.Numbers*1000) AS SumNumber, dbo.HistoryData.ClassID AS ID, dbo.ClassInfo.CName, SUM(dbo.HistoryData.EatNumber) 
                      AS PersonNum
FROM         dbo.HistoryData INNER JOIN
                      dbo.ClassInfo ON dbo.HistoryData.ClassID = dbo.ClassInfo.id INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id
WHERE     (dbo.HistoryData.RAWID = - 99) ");
            sql.Append(where);
            sql.Append(@" GROUP BY dbo.HistoryData.ClassID, dbo.ClassInfo.CName");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        public DataTable GerPersonOilAvgGroupByClassID(string where, DateTime DTIME)
        {
            string outdatestr = string.Format(" and Year(OutDate) = {0} ", DTIME.Year);
            string datestr = string.Format(" and Year(Date) = {0} ", DTIME.Year);
            if (DTIME.Second != 11)
            {
                outdatestr = " AND Month(outDate)=" + DTIME.Month;
                datestr = " AND Month(Date)=" + DTIME.Month;
            }
            StringBuilder sql1 = new StringBuilder();
            sql1.Append("select  a.SumNumber,ClassInfo.id,b.PersonNum,ClassInfo.CName from (");
            sql1.Append("select Sum(SumNumber) AS SumNumber,ClassID from FromRecords WHERE (dbo.FromRecords.RAWIDs = 20 OR dbo.FromRecords.RAWIDs = 25) ");
            sql1.Append(outdatestr);
            sql1.Append("group by ClassID) as a left join (");
            sql1.Append("select SUM(QC+persons+Catering) AS PersonNum,ClassID FROM [View_Dynamic] WHERE 1=1 ");
            sql1.Append(datestr);
            sql1.Append(" group by ClassID) as b on a.ClassID=b.ClassID inner join ClassInfo on ClassInfo.id=a.ClassID ");
            sql1.Append(where);
            

            //StringBuilder sql = new StringBuilder("SELECT ClassInfo.CName,ClassInfo.ID, Sum(dbo.FromRecords.SumNumber) AS SumNumber ,");
            //sql.Append("SUM([View_Dynamic].QC+[View_Dynamic].persons+[View_Dynamic].Catering) AS PersonNum FROM dbo.FromRecords Left JOIN dbo.ClassInfo ON dbo.ClassInfo.ID = dbo.FromRecords.ClassID ");
            //sql.Append(" Left JOIN [View_Dynamic] ON dbo.FromRecords.ClassID = [View_Dynamic].ClassID AND [View_Dynamic].Date = dbo.FromRecords.OutDate ");
            //sql.Append(" WHERE (dbo.FromRecords.RAWIDs = 20 OR dbo.FromRecords.RAWIDs = 25)");
            //sql.Append(where);
            //sql.Append(" GROUP BY ClassInfo.CName,ClassInfo.ID");
            return DbHelperSQL.Query(sql1.ToString()).Tables[0];
        }
        public DataTable GerPersonOilAvgHistoryGroupByGroupID(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT SUM(dbo.HistoryData.Numbers*1000) AS SumNumber, SUM(dbo.HistoryData.EatNumber) AS PersonNum, dbo.OperationArea.AreaName, 
                      dbo.ClassInfo.AreaID
FROM         dbo.HistoryData INNER JOIN
                      dbo.ClassInfo ON dbo.HistoryData.ClassID = dbo.ClassInfo.id INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id
WHERE     (dbo.HistoryData.RAWID = - 99)  ");
            sql.Append(where);
            sql.Append(@" GROUP BY dbo.OperationArea.AreaName, dbo.ClassInfo.AreaID");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        public DataTable GerPersonOilAvgGroupByGroupID(string where, DateTime DTIME)
        {
            string outdatestr = string.Format(" and Year(OutDate) = {0} ", DTIME.Year);
            string datestr = string.Format(" and Year(Date) = {0} ", DTIME.Year);
            if (DTIME.Second != 11)
            {
                outdatestr = " AND Month(outDate)=" + DTIME.Month;
                datestr = " AND Month(Date)=" + DTIME.Month;
            }
            StringBuilder sql1 = new StringBuilder();
            sql1.Append("select SUM(SumNumber) AS SumNumber, SUM(PersonNum) AS PersonNum,AreaName,AreaID from( ");
            sql1.Append("select  a.SumNumber,b.PersonNum,View_CompanyClass.AreaName,View_CompanyClass.AreaID from (");
            sql1.Append("select Sum(SumNumber) AS SumNumber,ClassID from FromRecords WHERE (dbo.FromRecords.RAWIDs = 20 OR dbo.FromRecords.RAWIDs = 25) ");
            sql1.Append(outdatestr);
            sql1.Append("group by ClassID) as a left join (");
            sql1.Append("select SUM(QC+persons+Catering) AS PersonNum,ClassID FROM [View_Dynamic] WHERE 1=1 ");
            sql1.Append(datestr);
            sql1.Append(" group by ClassID) as b on a.ClassID=b.ClassID inner join View_CompanyClass on View_CompanyClass.ClassID=a.ClassID ");
            sql1.Append(where);
            sql1.Append(") as c group by AreaID,AreaName ");

            //StringBuilder sql = new StringBuilder("SELECT View_CompanyClass.AreaName,View_CompanyClass.AreaID, Sum(CONVERT(decimal,dbo.FromRecords.Numbers))*4*4600 AS SumNumber ,");
            //sql.Append("SUM(dbo.Dynamic.number) AS PersonNum FROM dbo.FromRecords Left JOIN dbo.View_CompanyClass ON dbo.View_CompanyClass.ClassID = dbo.FromRecords.ClassID ");
            //sql.Append(" Left JOIN dbo.Dynamic ON dbo.FromRecords.ClassID = dbo.Dynamic.ClassID AND dbo.Dynamic.Date = dbo.FromRecords.OutDate ");
            //sql.Append(" WHERE (dbo.FromRecords.RAWIDs = 20 OR dbo.FromRecords.RAWIDs = 25)");
            //sql.Append(where);
            //sql.Append(" GROUP BY View_CompanyClass.AreaName,View_CompanyClass.AreaID");
            return DbHelperSQL.Query(sql1.ToString()).Tables[0];
        }

        public DataTable GetAreaOilAvg(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT SUM(CONVERT(decimal(18,2), dbo.FromRecords.SumNumber)) / SUM(CONVERT(decimal, 
                      dbo.Dynamic.Number)+CONVERT(decimal,dbo.Dynamic.Catering)) AS 'PersonAvg'
                      FROM dbo.ClassInfo INNER JOIN
                      dbo.Dynamic ON dbo.ClassInfo.Areaid = dbo.Dynamic.GroupID INNER JOIN
                      dbo.FromRecords ON dbo.ClassInfo.AreaID = dbo.FromRecords.GroupID AND YEAR(dbo.Dynamic.Date) = YEAR(dbo.FromRecords.OutDate) AND 
                      MONTH(dbo.FromRecords.OutDate) = MONTH(dbo.Dynamic.Date) INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id WHERE (dbo.FromRecords.RAWIDs = 20 OR dbo.FromRecords.RAWIDs = 25)  ");
            sql.Append(where);
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获取油脂类的消耗统计
        /// </summary>
        /// <param name="areaid"></param>
        /// <param name="classid"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public DataTable GetClassPersonOilByYear(int areaid, int classid, DateTime dtime)
        {
            StringBuilder sql = new StringBuilder();
            string where = "";
            string outdatestr = "";
            string datestr = "";
            if (areaid > 0) where += " AND GroupID=" + areaid + " ";
            if (classid > 0) where += " AND ClassID=" + classid + " ";
            if (dtime.Second != 11)
            {
                outdatestr = " AND Month(outDate)=" + dtime.Month;
                datestr = " AND Month(Date)=" + dtime.Month;
            }

            sql.Append(" select a.*,b.EatNumber from (");
            sql.Append(" select ClassID,sum(SumNumber) as Numbers from FromRecords ");
            sql.Append(" where (RAWIDs=20 or RAWIDs=25) AND Year(outDate)=" + dtime.Year + outdatestr + where);
            sql.Append(" group by ClassID) as a left join (");
            sql.Append(" select ClassID,sum(QC+persons+Catering) as EatNumber from [View_Dynamic] where YEAR(Date)=" + dtime.Year + datestr + " ");
            sql.Append(where);
            sql.Append(" group by ClassID ) as b on a.ClassID=b.ClassID ");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获取作业区油脂类的消耗统计
        /// </summary>
        /// <param name="areaid"></param>
        /// <param name="year"></param>
        /// <returns></returns>
        public DataTable GetGroupPersonOilByYear(int areaid, DateTime dtime)
        {
            StringBuilder sql = new StringBuilder();
            string wehre = " AND Year(outDate)=" + dtime.Year;
            if (dtime.Second != 11)
            {
                wehre += " AND Month(outDate)=" + dtime.Month;
            }
            sql.Append(" select a.Numbers,b.EatNumber from (");
            sql.Append(" select GroupID,sum(SumNumber) as Numbers from FromRecords ");
            sql.Append(" where (RAWIDs=20 or RAWIDs=25) AND GroupID=" + areaid + " AND Year(outDate)=" + dtime.Year);
            sql.Append(" group by GroupID) as a left join (");
            sql.Append(" select GroupID,sum(Number+Catering) as EatNumber from [Dynamic] where 1=1 ");
            sql.Append(" AND GroupID=" + areaid + " group by GroupID ) as b on a.GroupID=b.GroupID ");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        #endregion

        #region 平台人均分类对比
        public DataTable GetPerson_Avg_ContrastHistoryByYear(string strarealist, string[] dTime, string groupid, string classId, int[] rawId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT     sum(dbo.HistoryData.Numbers) AS SumNumber, YEAR(dbo.HistoryData.InDate) AS yeardate, dbo.RawMaterials.TypeName, dbo.ClassInfo.CName, 
                      dbo.OperationArea.id AS groupid, dbo.ClassInfo.id AS classid
FROM         dbo.RawMaterials INNER JOIN
                      dbo.HistoryData ON dbo.RawMaterials.id = dbo.HistoryData.RAWID INNER JOIN
                      dbo.ClassInfo ON dbo.HistoryData.ClassID = dbo.ClassInfo.id INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id where 1=1 ");
            if (dTime.Count() == 1)
            {
                strSql.Append(" and year(InDate) = " + dTime[0]);
            }
            else
            {
                strSql.Append(" and (");
                for (int i = 0; i < dTime.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append("year(InDate) = " + dTime[i]);
                    }
                    else
                    {
                        strSql.Append("or year(InDate) = " + dTime[i]);
                    }
                }
                strSql.Append(" )");
            }
            if (!string.IsNullOrEmpty(groupid) && !groupid.Equals("-10"))
            {
                strSql.Append(" and dbo.OperationArea.id = " + groupid);
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                strSql.Append(string.Format(" and dbo.OperationArea.id IN ( {0} )", strarealist));
            }

            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(" and dbo.ClassInfo.id = " + classId);
            }

            strSql.Append(@" group by YEAR(dbo.HistoryData.InDate), dbo.RawMaterials.TypeName, dbo.ClassInfo.CName, 
                      dbo.OperationArea.id, dbo.ClassInfo.id");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        public DataTable GetPerson_Avg_ContrastByYearHistory(string strarealist, string[] dTime, string groupid, string classId, int[] rawId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT     dbo.ClassInfo.CName,dbo.HistoryData.ClassID, dbo.HistoryData.RAWID as RAWTID, sum(dbo.HistoryData.Numbers /dbo.HistoryData.EatNumber) * 1000 as SumNumber, 
                      year(dbo.HistoryData.InDate) as yeardate, dbo.RawMaterials.TypeName, dbo.ClassInfo.AreaID AS groupid
FROM         dbo.HistoryData INNER JOIN
                      dbo.ClassInfo ON dbo.HistoryData.ClassID = dbo.ClassInfo.id INNER JOIN
                      dbo.RawMaterials ON dbo.HistoryData.RAWID = dbo.RawMaterials.id INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id where 1 = 1 ");

            if (dTime.Count() == 1)
            {
                strSql.Append(" and year(InDate) = " + dTime[0]);
            }
            else
            {
                strSql.Append(" and (");
                for (int i = 0; i < dTime.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append("year(InDate) = " + dTime[i]);
                    }
                    else
                    {
                        strSql.Append("or year(InDate) = " + dTime[i]);
                    }
                }
                strSql.Append(" )");
            }
            if (!string.IsNullOrEmpty(groupid) && !groupid.Equals("-10"))
            {
                strSql.Append(" and dbo.ClassInfo.AreaID = " + groupid);
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                strSql.Append(string.Format(" and dbo.ClassInfo.AreaID IN ( {0} )", strarealist));
            }

            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(" and dbo.HistoryData.ClassID = " + classId);
            }
            strSql.Append(@" group by year(dbo.HistoryData.InDate),dbo.ClassInfo.CName,dbo.HistoryData.ClassID,dbo.HistoryData.RAWID,dbo.RawMaterials.TypeName
,dbo.ClassInfo.AreaID ");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }
        /// <summary>
        /// 平台人均分类对比
        /// </summary>
        /// <param name="dTime">时间</param>
        /// <param name="classId">班组id，没有条件传null</param>
        /// <param name="rawTId">原料大类，没有条件传null</param>
        /// <returns></returns>
        public DataTable GetPerson_Avg_ContrastByYear(string strarealist, string[] dTime, string groupid, string classId, int[] rawId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select year(OutDate) as yeardate,groupid,classid,cname,typename,RAWTID,sum(SumNumber) as SumNumber from View_Xiaohao_Contrast where 1 = 1 ");

            if (dTime.Count() == 1)
            {
                strSql.Append(" and year(OutDate) = " + dTime[0]);
            }
            else
            {
                strSql.Append(" and (");
                for (int i = 0; i < dTime.Count(); i++)
                {
                    if (i == 0)
                    {
                        strSql.Append("year(OutDate) = " + dTime[i]);
                    }
                    else
                    {
                        strSql.Append("or year(OutDate) = " + dTime[i]);
                    }
                }
                strSql.Append(" )");
            }
            if (!string.IsNullOrEmpty(groupid) && !groupid.Equals("-10"))
            {
                strSql.Append(" and GroupID = " + groupid);
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                strSql.Append(string.Format(" and GroupID IN ( {0} )", strarealist));
            }

            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(" and ClassID = " + classId);
            }
            if (rawId != null)
            {
                if (rawId.Count() == 1)
                {
                    strSql.Append(" and RAWTID = " + rawId[0]);
                }
                else
                {
                    strSql.Append(" and (");
                    for (int i = 0; i < rawId.Count(); i++)
                    {
                        if (i == 0)
                        {
                            strSql.Append(" RAWTID = " + rawId[i]);
                        }
                        else
                        {
                            strSql.Append(" or RAWTID = " + rawId[i]);
                        }
                    }
                    strSql.Append(" )");
                }
            }
            strSql.Append(" group by groupid,classid,cname,year(OutDate),RAWTID,typename ");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }
        #endregion

        #region 人均消耗统计
        public DataTable GetRenShuHistory(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT     dbo.HistoryData.EatNumber
FROM         dbo.HistoryData INNER JOIN
                      dbo.RawMaterials ON dbo.HistoryData.RAWID = dbo.RawMaterials.id INNER JOIN
                      dbo.ClassInfo ON dbo.HistoryData.ClassID = dbo.ClassInfo.id INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id
GROUP BY dbo.HistoryData.EatNumber where dbo.HistoryData.RAWID <> -99 ");
            sql.Append(where);
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        public DataTable GetRenJunXHTongjiByYearHistory(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT SUM(dbo.HistoryData.Numbers*1000) / SUM(dbo.HistoryData.EatNumber) AS Numbers, dbo.RawMaterials.id as RAWTID, MONTH(dbo.HistoryData.InDate) AS numMonth, 
                      SUM(dbo.HistoryData.EatNumber) AS EatNumber
FROM         dbo.HistoryData INNER JOIN
                      dbo.RawMaterials ON dbo.HistoryData.RAWID = dbo.RawMaterials.id INNER JOIN
                      dbo.ClassInfo ON dbo.HistoryData.ClassID = dbo.ClassInfo.id INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id
WHERE     (dbo.HistoryData.RAWID <> - 99) ");
            sql.Append(where);
            sql.Append(" GROUP BY dbo.RawMaterials.id, MONTH(dbo.HistoryData.InDate)");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];

        }
        /// <summary>
        /// 人均消耗统计
        /// </summary>
        /// <param name="dTime">时间</param>
        /// <param name="classId">班组id，没有条件传null</param>
        /// <param name="rawTId">原料大类，没有条件传null</param>
        /// <returns></returns>
        public DataTable GetRenJunXHTongjiByYear(string year, string strarealist, string groupid, string classId, int[] rawId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select chmonth,numMonth,RAWTID,sum(SumNumber) as SumNumber from Person_Avg_Contrast where 1 = 1 ");

            if (!string.IsNullOrEmpty(year))
            {
                strSql.Append(" and outdate = " + year);
            }
            if (!string.IsNullOrEmpty(groupid) && !groupid.Equals("-10"))
            {
                strSql.Append(" and GroupID = " + groupid);
            }
            else
            {
                strSql.Append(" and GroupID IN ( " + strarealist + ")");
            }
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(" and ClassID = " + classId);
            }
            if (rawId != null)
            {
                if (rawId.Count() == 1)
                {
                    strSql.Append(" and RAWTID = " + rawId[0]);
                }
                else
                {
                    strSql.Append(" and (");
                    for (int i = 0; i < rawId.Count(); i++)
                    {
                        if (i == 0)
                        {
                            strSql.Append(" RAWTID = " + rawId[i]);
                        }
                        else
                        {
                            strSql.Append(" or RAWTID = " + rawId[i]);
                        }
                    }
                    strSql.Append(" )");
                }
            }
            strSql.Append(" group by chmonth,numMonth,RAWTID ");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }
        #endregion

        #region 人均分类统计
        /// <summary>
        /// 人均分类统计
        /// </summary>
        /// <returns></returns>
        public DataTable GetPersonAvgContrastForLineChar(string strclasslist, string year, string classId, int[] rawId)
        {
            StringBuilder strSql = new StringBuilder(@"SELECT  SUM(CONVERT(int, SumNumber)) AS number, TypeName, MONTH(OutDate) AS mon, YEAR(OutDate) AS year, RAWTID
FROM         dbo.View_Xiaohao_Contrast where 1=1 ");
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(" and ClassID = " + classId);
            }
            else
            {
                strSql.Append(" and ClassID IN( " + strclasslist + ") ");
            }
            if (!string.IsNullOrEmpty(year))
            {
                strSql.Append(" and YEAR(OutDate) = " + year);
            }
            if (rawId != null && rawId.Count() > 0)
            {
                if (rawId.Count() == 1)
                {
                    strSql.Append(" and RAWTID = " + rawId[0]);
                }
                else
                {
                    strSql.Append(" and (");
                    for (int i = 0; i < rawId.Count(); i++)
                    {
                        if (i == 0)
                        {
                            strSql.Append(" RAWTID = " + rawId[i]);
                        }
                        else
                        {
                            strSql.Append(" or RAWTID = " + rawId[i]);
                        }
                    }
                    strSql.Append(" )");
                }
            }
            strSql.Append(@"GROUP BY TypeName, MONTH(OutDate), YEAR(OutDate), RAWTID
ORDER BY mon");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }

        public DataTable GetPersonAvgContrastForLineCharHistory(string strclasslist, string year, string classId, int[] rawId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append(@"SELECT     dbo.RawMaterials.TypeName, SUM(dbo.HistoryData.Numbers / dbo.HistoryData.EatNumber) AS number, MONTH(dbo.HistoryData.InDate) AS mon, 
                      YEAR(dbo.HistoryData.InDate) AS year, dbo.HistoryData.RAWID
FROM         dbo.HistoryData INNER JOIN
                      dbo.ClassInfo ON dbo.HistoryData.ClassID = dbo.ClassInfo.id INNER JOIN
                      dbo.OperationArea ON dbo.ClassInfo.AreaID = dbo.OperationArea.id INNER JOIN
                      dbo.RawMaterials ON dbo.HistoryData.RAWID = dbo.RawMaterials.id where 1=1 ");
            if (!string.IsNullOrEmpty(classId) && !classId.Equals("-10"))
            {
                strSql.Append(" and dbo.HistoryData.ClassID = " + classId);
            }
            else
            {
                strSql.Append(" and dbo.HistoryData.ClassID IN( " + strclasslist + ") ");
            }
            if (!string.IsNullOrEmpty(year))
            {
                strSql.Append(" and YEAR(dbo.HistoryData.InDate) = " + year);
            }
            strSql.Append(@" GROUP BY dbo.RawMaterials.TypeName, MONTH(dbo.HistoryData.InDate), YEAR(dbo.HistoryData.InDate), dbo.HistoryData.RAWID");
            return DbHelperSQL.Query(strSql.ToString()).Tables[0];
        }
        #endregion

        #region 人均食用油汇总

        //public DataTable GetOilPersonAvgContrant(string where)
        //{
        //    SqlParameter[] parm = new SqlParameter[]
        //    {
        //        new SqlParameter("@strwhere",where)
        //    };
        //    return DbHelperSQL.RunProcedureReDataTable("Proc_OilPersonAvg", parm);
        //}

        /// <summary>
        /// 获取每月消耗值
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable GetXiaoHaoAvg(string where)
        {
            string sql = "select f.GroupID,f.ClassID,SUM(f.SumNumber) as SumNumber,YEAR(f.OutDate) as [YEAR]," +
            " Month(f.OutDate) as [Month] from View_FromRecords as f " +
            " where (RAWIDs='20' or RAWIDs='25') " + where +
            " group by f.GroupID,f.ClassID,YEAR(f.OutDate),Month(f.OutDate)";
            return DbHelperSQL.Query(sql).Tables[0];
        }
        /// <summary>
        /// 获取每月要料值
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable GetPurchase(string where)
        {
            string sql = "select p.GroupID,p.ClassID,sum(Convert(decimal(18,2),case when p.temp1 is null then '0' else p.temp1 end)) as GrogramNumber," +
                " YEAR(p.[Date]) as [YEAR],Month(p.[Date]) as [Month] from View_PurchaseInfo as p" +
                " where (RAWIDs='20' or RAWIDs='25') " + where +
                " group by p.GroupID,p.ClassID,YEAR(p.[Date]),Month(p.[Date])";
            return DbHelperSQL.Query(sql).Tables[0];
        }

        /// <summary>
        /// 获取每月就餐人数
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable GetDynamic(string where)
        {
            string sql = "select d.GroupID,d.ClassID,YEAR(d.[Date]) as [YEAR],Month(d.[Date]) as [Month],sum(d.Number) as Number " +
                " from View_Dynamic as d where 1=1 " + where +
                " group by d.GroupID,d.ClassID,YEAR(d.[Date]),Month(d.[Date])";
            return DbHelperSQL.Query(sql).Tables[0];
        }

        /// <summary>
        /// 获取2011年以前的食用油汇总
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable GetOilPersonAvgHistory(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"Declare @sql varchar(8000)
Set @sql = 'Select GroupID,ClassID,AreaName,CName'
Select @sql = @sql + ',sum(case NumMonth when '''+NumMonth+''' then Numbers * 1000 else 0 end) ['+NumMonth+'r]'
from (select [Month] as NumMonth from dayofmonth) as View_OilPersonAvgContrant 
Select @sql = @sql+' from View_OilPersonAvgHistroy where 1=1 
 ");
            sql.Append(where);
            sql.Append(@" group by AreaName,CName,GroupID,ClassID' 
Exec (@sql)");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获取2011年以前的食用油汇总
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable GetOilPersonAvgHistoryForChar(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select sum(Numbers) * 1000 as Numbers ,nummonth from View_OilPersonAvgHistroy where 1 = 1 ");
            sql.Append(where);
            sql.Append("  group by nummonth");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        #endregion

        #region 成本数据统计

        /// <summary>
        /// 获取库存总量，期初与期末可共用
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public decimal GetCountResult(DateTime datetime, int groupid)
        {
            StringBuilder str = new StringBuilder();
            string where = "";
            if (datetime.Year > 1949)
            {
                where = " AND AddDate<'" + datetime.AddDays(1).ToString("yyyy-MM-dd") + "'";
            }
            if (groupid > 0)
            {
                where += " AND ClassID IN (SELECT ID FROM ClassInfo WHERE AreaID=" + groupid + " ) ";
            }
            str.Append("SELECT SUM(Price*Balance) FROM (SELECT *,RANK() OVER(PARTITION BY ClassID,RawID,InDBID ORDER BY AddDate Desc) AS [Rank] ");
            str.Append("FROM Balance WHERE 1=1 " + where + ") AS A WHERE [Rank]=1 ");
            Object single = DbHelperSQL.GetSingle(str.ToString());
            decimal count = 0;
            if (single != null)
            {
                decimal.TryParse(single.ToString(), out count);
            }
            return count;
        }

        /// <summary>
        /// 获取库存总量，期初与期末可共用
        /// </summary>
        /// <param name="datetime"></param>
        /// <param name="RAWTID"></param>
        /// <param name="classid"></param>
        /// <returns></returns>
        public decimal GetCountResultByClass(DateTime datetime, int RAWTID, int classid)
        {
            StringBuilder str = new StringBuilder();
            string where = "";
            if (datetime.Year > 1949)
            {
                where = " AND B.AddDate<'" + datetime.AddDays(1).ToString("yyyy-MM-dd") + "'";
            }
            if (classid > 0)
            {
                where += " AND B.ClassID =" + classid + " ";
            }
            if (RAWTID > 0)
            {
                where += " AND R.RAWTID =" + RAWTID + " ";
            }
            str.Append("SELECT SUM(Price*Balance) FROM (SELECT B.*,R.RAWTID,RANK() OVER(PARTITION BY B.ClassID,B.RawID,B.InDBID ORDER BY AddDate Desc) AS [Rank] ");
            str.Append("FROM Balance AS B LEFT JOIN RawMaterial AS R ON B.RawID=R.ID " + where + ") AS A WHERE [Rank]=1 ");
            Object single = DbHelperSQL.GetSingle(str.ToString());
            decimal count = 0;
            if (single != null)
            {
                decimal.TryParse(single.ToString(), out count);
            }
            return count;
        }

        /// <summary>
        /// 获取就餐人数
        /// </summary>
        /// <param name="where"></param>
        /// <returns></returns>
        public DataTable GetAteNumberForCost(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select sum(Number)as Number from view_dynamic where 1=1 ");
            sql.Append(where);
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        #endregion

        #region 成本比例
        public DataTable GetViewFromRecords(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from View_FromRecordsForCostPieChar where 1=1 ");
            if (!string.IsNullOrEmpty(where))
            {
                sql.Append(where);
            }
            DataTable dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }
        #endregion

        #region 月成本明细
        public DataTable GetCostDataPersonAvg(string wheredy, string wherefr)
        {
            DataTable dt = null;
            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format("select *,AmountMoney/(select sum(Number) from view_Dynamic where 1=1 {0})as PersonAvg from View_CostDataPersonAvg where 1=1 {1}", wheredy, wherefr));
            dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }

        #endregion

        #region 消耗分类横向对比图
        /// <summary>
        /// 消耗分类横向对比图
        /// </summary>
        /// <param name="rawTID"></param>
        /// <param name="classId"></param>
        /// <param name="dTime"></param>
        /// <returns></returns>
        public DataTable GetXiaohaoTypeCrossChar(string where)
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT SUM(CONVERT(decimal(18,2), F.Numbers)*F.Price) AS Jine,"
                      + "YEAR(F.OutDate) AS [YEAR], C.CName FROM dbo.FromRecords AS F LEFT JOIN "
                      + " dbo.ClassInfo AS C ON F.ClassID = C.id WHERE (1 = 1) ");
            sql.Append(where);
            sql.Append(" GROUP BY YEAR(F.OutDate),C.CName");
            dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }

        #endregion

        #region 消耗分类纵向对比图

        public DataTable GetXiaohaoTypePortait(int[] RAWTID, string CLASSID, DateTime DTIME)
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            if (RAWTID != null && RAWTID.Count() > 0)
            {
                for (int i = 0; i < RAWTID.Count(); i++)
                {
                    sql.Append(string.Format(" or RAWTID = {0}", RAWTID[i]));
                }
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                sql.Append(string.Format(" and classID={0}", CLASSID));
            }
            if (DTIME != null)
            {
                sql.Append(string.Format(" and Year(outdate) = {0}", DTIME.Year));
            }
            SqlParameter[] parm = new SqlParameter[]
            {
                new SqlParameter("@strwhere",sql.ToString())
            };
            dt = DbHelperSQL.RunProcedureReDataTable("Proc_XiaohaoTypePortait", parm);
            return dt;
        }

        /// <summary>
        /// 第三方折线图
        /// </summary>
        /// <param name="RAWTID"></param>
        /// <param name="CLASSID"></param>
        /// <param name="AREAID"></param>
        /// <param name="CompanyID"></param>
        /// <param name="DTIME"></param>
        /// <returns></returns>
        public DataTable GetXiaohaoTypePortaitThird(int[] RAWTID, string CLASSID, string AREAID, string CompanyID, DateTime DTIME)
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder("SELECT RAWTID,TypeName,SUM(Price*Numbers) AS SumNumber,Month(OutDate) AS YMonth FROM View_XiaohaoTypePortait WHERE 1=1 ");
            if (RAWTID != null && RAWTID.Count() > 0)
            {
                sql.Append(" AND (");
                for (int i = 0; i < RAWTID.Count(); i++)
                {
                    if (i == 0) sql.Append(string.Format("RAWTID = {0}", RAWTID[i])); else sql.Append(string.Format(" or RAWTID = {0}", RAWTID[i]));
                }
                sql.Append(" ) ");
            }
            if (!string.IsNullOrEmpty(CompanyID) && !CompanyID.Equals(""))
            {
                sql.Append(string.Format(" and CompanyID={0}", CompanyID));
            }
            if (!string.IsNullOrEmpty(AREAID) && !AREAID.Equals("-10"))
            {
                sql.Append(string.Format(" and AreaID={0}", AREAID));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                sql.Append(string.Format(" and classID={0}", CLASSID));
            }

            if (DTIME != null)
            {
                sql.Append(string.Format(" and Year(outdate) = {0}", DTIME.Year));
            }
            sql.Append(" GROUP BY RAWTID,TypeName,Month(OutDate) ");
            dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }


        #endregion

        #region 年度伙食统计表
        public DataTable GetHallYearAte(string whereFromRecords)
        {
            DataTable dt = new DataTable();
            StringBuilder sql = new StringBuilder();
            sql.Append(@"SELECT     SUM(CONVERT(decimal, dbo.FromRecords.Numbers) * CONVERT(decimal, dbo.FromRecords.Price)) AS Numbers, MONTH(dbo.FromRecords.OutDate) 
                      AS numMonth, dbo.getChNum(MONTH(dbo.FromRecords.OutDate)) AS chmonth, dbo.ClassInfo.CName,dbo.FromRecords.ClassID
FROM         dbo.FromRecords INNER JOIN
                      dbo.ClassInfo ON dbo.FromRecords.ClassID = dbo.ClassInfo.id where 1=1 ");
            sql.Append(whereFromRecords);
            sql.Append("GROUP BY MONTH(dbo.FromRecords.OutDate), dbo.ClassInfo.CName, dbo.getChNum(MONTH(dbo.FromRecords.OutDate)),dbo.FromRecords.ClassID");
            dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }
        #endregion


        #region 自付费杂品明细
        public DataTable GetGroceryDetail(string whereClause)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format("select ClassID,GroupID,JinE,[Year],[Month],SUMPerson from View_GroceryDetail WHERE 1=1 {0} ", whereClause));
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        #endregion

        #region 就餐人员年/月统计
        /// <summary>
        /// 就餐人员年/月统计
        /// </summary>
        /// <param name="GROUPID">作业区ID</param>
        /// <param name="CLASSID">班组ID</param>
        /// <param name="DTIME">时间</param>
        /// <returns>DataTable</returns>
        public DataTable GetDynamicAte(string strclasslist, string GROUPID, string CLASSID, DateTime DTIME)
        {
            DataTable dt = null;
            StringBuilder sql = new StringBuilder();
            if (DTIME.Second.Equals(11))//按年
            {
                sql.Append("select sum(Persons)as number,sum(Catering) as eat,sum(QC)as getmoney,monthofdate,yearofdate,sum(day) as day from view_dynamic");
            }
            else//按月
            {
                sql.Append("select sum(Persons)as number,sum(Catering) as eat,sum(QC)as getmoney,monthofdate,yearofdate,day from view_dynamic");

            }
            sql.Append(" where 1=1 ");
            if (!string.IsNullOrEmpty(GROUPID) && !GROUPID.Equals("-10"))
            {
                sql.Append(string.Format(" and GroupID={0}", GROUPID));
            }
            else if (!string.IsNullOrEmpty(strclasslist))
            {
                sql.Append(string.Format(" and GroupID IN ({0})", strclasslist));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                sql.Append(string.Format(" and ClassID={0}", CLASSID));
            }
            if (DTIME != null && !DTIME.Year.Equals(1949))
            {
                sql.Append(string.Format(" and yearofdate={0}", DTIME.Year));
                if (!DTIME.Second.Equals(11))
                {
                    sql.Append(string.Format(" and monthofdate={0}", DTIME.Month));
                }
            }
            sql.Append(" group by yearofdate,monthofdate");
            if (!DTIME.Second.Equals(11))
            {
                sql.Append(",day");
            }
            dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }

        public DataTable GetDynamicAte(string strclasslist, string GROUPID, string CLASSID, DateTime DTStart, DateTime DTEnd)
        {
            DataTable dt = null;
            StringBuilder sql = new StringBuilder();
            if (DTStart.Year < 2012 || DTEnd.Year < 2012) return null;
            sql.Append("select sum(Persons)as number,sum(Catering) as eat,sum(QC)as getmoney,date from view_dynamic");
            sql.Append(" where 1=1 ");
            if (!string.IsNullOrEmpty(GROUPID) && !GROUPID.Equals("-10"))
            {
                sql.Append(string.Format(" and GroupID={0}", GROUPID));
            }
            else if (!string.IsNullOrEmpty(strclasslist))
            {
                sql.Append(string.Format(" and GroupID IN ({0})", strclasslist));
            }
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                sql.Append(string.Format(" and ClassID={0}", CLASSID));
            }
            sql.Append(string.Format(" and date >= '{0}' and date <= '{1}' ", DTStart.ToString("yyyy-MM-dd"), DTEnd.ToString("yyyy-MM-dd")));
            sql.Append(" group by date order by date ");
            dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }

        #endregion

        #region 配餐人员动态

        public DataTable GetPersonCount()
        {
            DataTable dt = null;
            string sql = @"SELECT     dbo.Company.CName, count(*) as Number
FROM         dbo.Company INNER JOIN
                      dbo.Personnel ON dbo.Company.id = dbo.Personnel.CID group by Cname";
            dt = DbHelperSQL.Query(sql).Tables[0];
            return dt;
        }

        public DataTable GetSetingPerson(string CLASSID, string isWorking)
        {
            DataTable dt = null;
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from View_Personnel where 1=1 ");
            if (!string.IsNullOrEmpty(CLASSID) && !CLASSID.Equals("-10"))
            {
                sql.Append(string.Format(" and ClassID={0}", CLASSID));
            }
            if (!string.IsNullOrEmpty(isWorking))
            {
                sql.Append(string.Format(" and IsWorking = {0}", isWorking));
            }
            dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }

        public DataTable GetCompanyPerson(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select * from View_Personnel where 1=1 and ClassID = 50 ");
            sql.Append(where);
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        public DataTable GetPersonModel(string id)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append(string.Format("select * from View_Personnel where id = {0}", id));
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }

        /// <summary>
        /// 获取配餐人员动态列表
        /// </summary>
        /// <param name="GroupID">作业区ID</param>
        /// <param name="ClassID">班组ID</param>
        /// <param name="DTime">时间</param>
        /// <param name="isZiYingCompany">状态：true：包含自营公司、false：不包含自营公司</param>
        /// <returns></returns>
        public DataTable GetAttendanceRecordReport(string strarealist, string GroupID, string ClassID, DateTime DTime, bool isZiYingCompany)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select AreaID,AreaName,ClassID,CName,Pid,PName,SName,[Name],Date,State FROM View_PeiCanRenYuanDongTai WHERE 1=1 ");
            if (!isZiYingCompany) strSql.Append(" AND ClassID <> 50 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND AreaID = {0} ", GroupID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                strSql.Append(string.Format(" AND AreaID IN ({0}) ", strarealist));
            }
            if (!string.IsNullOrEmpty(ClassID) && !ClassID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND ClassId = {0} ", ClassID));
            }

            //AND Year(outDate)='2012' AND Month(outDate)='6'
            if (DTime > DateTime.MinValue)
            {
                strSql.Append(string.Format(" AND Date = '{0}' ", DTime.ToString("yyyy-MM-dd")));
            }

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        public DataTable GetZiYingCompanyAttendanceRecord()
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select distinct id AS PID,PName,SName,[Name] FROM View_Personnel WHERE ClassID=50 ");
            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 获取更公司人数统计
        /// </summary>
        /// <param name="GroupID"></param>
        /// <param name="ClassID"></param>
        /// <param name="DTime"></param>
        /// <returns></returns>
        public DataTable GetAttendanceRecordByMSN(string GroupID, string ClassID, DateTime DTime)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT SName,COUNT(PName) AS CountAll,COUNT(Working) AS CountWorking,COUNT(UNWorking) AS CountUNWorking" +
                " FROM (SELECT SName,PName,CASE WHEN State=1 THEN 1 ELSE NULL END AS Working," +
                " CASE WHEN State=0 THEN 1 ELSE NULL END AS UNWorking FROM View_PeiCanRenYuanDongTai WHERE ClassID <> 50 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND AreaID = {0} ", GroupID));
            }
            if (!string.IsNullOrEmpty(ClassID) && !ClassID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND ClassId = {0} ", ClassID));
            }

            //AND Year(outDate)='2012' AND Month(outDate)='6'
            if (DTime > DateTime.MinValue)
            {
                strSql.Append(string.Format(" AND Date = '{0}' ", DTime.ToString("yyyy-MM-dd")));
            }
            strSql.Append(") AS MyTable GROUP BY SName");

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 获取更公司人数统计
        /// </summary>
        /// <param name="DTime"></param>
        /// <returns></returns>
        public DataTable GetZiYingAttendanceRecordByMSN(DateTime DTime)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT SName,COUNT(PName) AS CountAll,COUNT(Working) AS CountWorking,COUNT(UNWorking) AS CountUNWorking" +
                " FROM (SELECT SName,PName,CASE WHEN State=1 THEN 1 ELSE NULL END AS Working," +
                " CASE WHEN State=0 THEN 1 ELSE NULL END AS UNWorking FROM View_PeiCanRenYuanDongTai WHERE ClassID = 50 ");
            //AND Year(outDate)='2012' AND Month(outDate)='6'
            if (DTime > DateTime.MinValue)
            {
                strSql.Append(string.Format(" AND Date = '{0}' ", DTime.ToString("yyyy-MM-dd")));
            }
            strSql.Append(") AS MyTable GROUP BY SName");

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 获取配餐人员动态列表
        /// </summary>
        /// <param name="GroupID">作业区ID</param>
        /// <param name="ClassID">班组ID</param>
        /// <param name="DTime">时间</param>
        /// <param name="isZiYingCompany">状态：true：包含自营公司、false：不包含自营公司</param>
        /// <returns></returns>
        public DataTable GetPeicanDongTaiByDate(string strarealist, string GroupID, string ClassID, DateTime DTime, bool isZiYingCompany)
        {
            DataTable dt = null;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("Select C.AreaID,O.AreaName,P.ClassID,C.CName,P.ID AS PID,P.[Name] AS PName,B.[Name],A.State, S.SName ");
            strSql.Append(" FROM dbo.Personnel AS P left join (Select * From dbo.AttendanceRecord where Date='" + DTime.ToString("yyyy-MM-dd") + "') AS A ON A.PID = P.id ");
            strSql.Append(" left join dbo.Service_Unit AS S ON P.ServiceUnitID = S.SID ");
            strSql.Append(" left join dbo.BranchPost AS B ON P.PID = B.id  ");
            strSql.Append(" left join dbo.ClassInfo AS C ON P.ClassID = C.ID ");
            strSql.Append(" left join dbo.OperationArea AS O ON C.AreaID = O.id WHERE 1=1 ");
            if (!isZiYingCompany) strSql.Append(" AND P.ClassID <> 50 ");
            if (!string.IsNullOrEmpty(GroupID) && !GroupID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND C.AreaID = {0} ", GroupID));
            }
            else if (!string.IsNullOrEmpty(strarealist))
            {
                strSql.Append(string.Format(" AND C.AreaID IN ({0}) ", strarealist));
            }
            if (!string.IsNullOrEmpty(ClassID) && !ClassID.Equals("-10"))
            {
                strSql.Append(string.Format(" AND P.ClassId = {0} ", ClassID));
            }

            dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
            return dt;
        }

        #endregion

        #region 月考勤
        public DataTable GetWorkOfMonth(string where)
        {
            DataTable dt = null;
            StringBuilder sql = new StringBuilder();
            sql.Append("select Name,ClassName,PName,Day(Date) as Date from View_AttendanceRecord where 1=1 ");
            sql.Append(where);
            dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }
        #endregion

        #region 年考勤
        public DataTable GetWorkOfYear(string where)
        {
            DataTable dt = null;
            StringBuilder sql = new StringBuilder();
            sql.Append("select [Name],PName,month(Date) as Date from View_AttendanceRecord where 1=1 ");
            sql.Append(where);
            sql.Append(" group by [Name],PName,month(Date)");
            dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }
        #endregion

        #region 服务比列汇总
        public DataTable GetServiceCollect(string where, string group)
        {
            //StringBuilder sql = new StringBuilder();
            //sql.Append("select ClassID,GroupID,yearofdate,CName,sum(Number) as Number,sum(Catering) as Catering,sum(Charge) as Charge,sum(HPersons) as HPersons,sum(QC) as QC,sum(persons) as persons from view_dynamic where 1=1");
            //sql.Append(where);
            //sql.Append(" group by ClassID,GroupID,CName,yearofdate" + group);
            //return DbHelperSQL.Query(sql.ToString()).Tables[0];
            StringBuilder sql = new StringBuilder();
            sql.Append(@"select CName,ClassID,sum(Catering) as Catering,Sum(persons) as Persons,sum(QC) as QC,sum(persons+QC) as Number,Sum(QC+Persons+Catering) as PersonCount from view_servicecollect where 1=1 ");
            sql.Append(where);
            sql.Append(" group by CName,ClassID");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        #endregion

        #region 全年服务总表
        public DataTable GetHallYearCollect(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select ClassID,GroupID,CName,monthofdate,sum(Number) as Number,sum(Catering) as Catering,sum(Charge) as Charge,sum(HPersons) as HPersons,sum(QC+persons) as QC from view_dynamic where 1=1 ");
            sql.Append(where);
            sql.Append("group by ClassID,GroupID,CName,monthofdate");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        #endregion

        #region 人均服务比例图
        /// <summary>
        /// 人均服务比例图
        /// </summary>
        /// <param name="where">and year(A.Date)=2012 and C.id=1 and B.id=45</param>
        /// <returns></returns>
        public DataTable GetPersonAvgService(string where)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("select B.AreaID AS GroupID,B.ID AS ClassID,Sum(A.persons+A.QC) AS Number,");
            sql.Append("Sum(A.Catering) AS Catering,B.CName from Dynamic AS A inner join classInfo AS B on A.ClassID=B.id");
            sql.Append(" inner join OperationArea AS c on b.AreaID=c.id where B.ID is not null " + where);
            sql.Append(" group by B.AreaID,B.ID,CName ");
            return DbHelperSQL.Query(sql.ToString()).Tables[0];
        }
        #endregion

        #region 日报统计

        /// <summary>
        /// 获取日报人数统计
        /// </summary>
        /// <param name="whereClause"></param>
        /// <returns></returns>
        public DataTable GetView_RiBaoData(string whereClause)
        {
            DataTable dt = null;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT GroupID,ClassID,AreaName,CName,[Date],[YEAR],[MONTH],[DAY],DingYuan,QiTa,PeiCanZaiGang FROM View_RiBao WHERE 1=1 ");
            sql.Append(whereClause);
            dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }

        /// <summary>
        /// 获取其他单位人员数据
        /// </summary>
        /// <param name="whereClause"></param>
        /// <returns></returns>
        public DataTable GetRiBaoData_Other(string whereClause)
        {
            DataTable dt = null;
            StringBuilder sql = new StringBuilder();
            sql.Append("SELECT GroupID,ClassID,[Dates],Eat,temp1 FROM View_RiBao_Other WHERE temp1 IS NOT NUll ");
            sql.Append(whereClause);
            dt = DbHelperSQL.Query(sql.ToString()).Tables[0];
            return dt;
        }

        #endregion
    }
}
